package com.alibaba.druid.sql;

public class TestTranslateGauss {

    public static void main(String[] args) {

        String mysql = "SELECT\n" +
                "        ep.*,\n" +
                "        GROUP_CONCAT(distinct r.ROLE_NAME separator ';') roleName,\n" +
                "        GROUP_CONCAT(distinct dept.dept_name separator ';') deptName,\n" +
                "        GROUP_CONCAT(distinct dept.dept_id separator ',') deptId\n" +
                "        FROM\n" +
                "        qx_ent_police ep\n" +
                "        left join qx_ent_police_dept pd on ep.id = pd.police_id\n" +
                "        LEFT JOIN qx_sys_dept dept ON pd.dept_id = dept.DEPT_ID\n" +
                "        left join qx_ent_police_role pr on ep.id = pr.police_id\n" +
                "        left join qx_ent_role r on pr.role_id = r.role_id\n" +
                "        WHERE\n" +
                "        ep.status != '-1'\n" +
                "        GROUP BY ep.id\n" +
                "        order by ep.id desc";

        String sql1 = "select distinct p.id, p.login_name, p.username, p.phone, p.sex, p.avatar\n" +
                "        from qx_ent_police_dept pd\n" +
                "        left join qx_ent_police p on p.id = pd.police_id\n" +
                "        where p.id is not null and dept_id in (\n" +
                "            select dept_id\n" +
                "            from qx_sys_dept\n" +
                "            where FIND_IN_SET(dept_id,\n" +
                "                  getDeptChilds(\n" +
                "                          (select GROUP_CONCAT(distinct prp.root_dept_id)\n" +
                "                           from qx_ent_police_role pr\n" +
                "                                    left join qx_ent_police_role_priv prp on prp.police_role_id = pr.id\n" +
                "                           where pr.police_id = (select id from qx_ent_police where login_name = #{loginName}))\n" +
                "                      ))\n" +
                "        )";

        String sql2 = "\n" +
                " SELECT p.username,\n" +
                "               COUNT(w.id) AS `status`,\n" +
                "               ha.address,\n" +
                "               ha.create_time AS date,\n" +
                "                SUBSTRING_INDEX(IFNULL(w.warn_remark,'111'),'-',-1)  as warnRemark\n" +
                "        FROM qx_apy_hold_action  ha\n" +
                "                 LEFT JOIN qx_sys_warn w ON w.action_id = ha.id\n" +
                "                 LEFT JOIN qx_ent_police p ON p.login_name = ha.holder\n" +
                "        where ha.action = 52\n" +
                "          AND ha.root_apply_num=(SELECT root_apply_num FROM qx_apy_transfer_apply WHERE apply_num=#{applyNum})\n" +
                "        GROUP BY ha.id\n" +
                "        ORDER BY ha.id DESC";

        /**
         * COALESCE 用于返回参数中的第一个非NULL值
         * extract函数提取日期时间的指定部分
         * EPOCH函数可以将TIMESTAMP转换为Unix时间戳
         * 新纪元时间 Epoch 是以 1970-01-01 00:00:00 UTC 为标准的时间，将目标时间与 1970-01-01 00:00:00
         * 时间的差值以秒来计算 ，单位是秒，可以是负值;
         */
        String sql3 = "SELECT\n" +
                "eg.*,\n" +
                "egm.model_name,\n" +
                "dept.dept_name,\n" +
                "safe.safe_name,\n" +
                "hac.repairCount,\n" +
                "COALESCE(shoot.shootCount,0) as shootCount,\n" +
                "COALESCE(EXTRACT(EPOCH FROM now()::TIMESTAMP(0) - (COALESCE(ha.action_time , eg.retrun_time) + INTERVAL '1 MONTH') )/(3600*24),0) as\n" +
                "leftRepairDays ,\n" +
                "COALESCE (ha.action_time , eg.retrun_time) as latestRepairTime,\n" +
                "COALESCE(gh.rfid = '' or gh.rfid is null  , '未智能改造','智能改造')\n" +
                "FROM\n" +
                "qx_ent_gun eg\n" +
                "LEFT JOIN qx_ent_gun_model egm ON eg.model_code = egm.model_code\n" +
                "LEFT JOIN qx_ent_gun_handle gh on eg.serial_number=gh.gun_number\n" +
                "LEFT JOIN qx_sys_dept dept ON eg.dept_id = dept.DEPT_ID\n" +
                "left join qx_ent_gun_safe safe on eg.safe_id = safe.id\n" +
                "LEFT JOIN (\n" +
                "select SUBSTRING_INDEX(group_concat(action_time order by `action_time` desc),',',1) as action_time,serial_number\n" +
                "from qx_apy_hold_action\n" +
                "where action = 60\n" +
                "GROUP BY serial_number) ha on ha.serial_number=eg.serial_number\n" +
                "LEFT JOIN (\n" +
                "SELECT COUNT(*) as repairCount ,serial_number from qx_apy_hold_action where action=59 GROUP BY serial_number\n" +
                ") hac on hac.serial_number=eg.serial_number\n" +
                "\n" +
                "LEFT JOIN (\n" +
                "SELECT COUNT(*) as shootCount ,serial_number from qx_apy_hold_action where action=1 GROUP BY serial_number\n" +
                ") shoot on shoot.serial_number=eg.serial_number\n" +
                "order by eg.id desc";

        String gaussSql = SQLUtils.translateMysqlToGaussDb(sql3);
        System.out.println(gaussSql);
    }
}
